var mysql = require('mysql');
const JSON5 = require('json5')
const fs = require('fs')
const path = require('path')
const jwt = require('jsonwebtoken')
function getJsonFile(filePath) {
  // 读取指定json文件
  var json = fs.readFileSync(path.resolve(__dirname, filePath), 'utf-8');
  // 解析并返回
  return JSON5.parse(json);
}
var EXPIRESD = 60 * 60 * 24;
var PRIVITE_KEY = 'secret';
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  port: '3306',
  database: 'rjgc',
  multipleStatements: true
});
connection.connect();
function Mysql() {
  this.findoneuser = function (peo, res) {
    var sql1 = 'SELECT * FROM user where (userName = ? and Password = ?)';
    var modSqlParams = [peo.username, peo.password];
    connection.query(sql1, modSqlParams, function (err, result) {

      if (err) {
        return;
      }
      if (result[0]) {
        var returnlogin = getJsonFile('./loginreturn');
        returnlogin.data.userid = result[0].userID;
        var y = { name: result[0].userID };
        //console.log(y);
        var token = jwt.sign(y, PRIVITE_KEY, { expiresIn: EXPIRESD });
        // console.log("token:");
        // console.log(token);
        returnlogin.data.token = token;
        res.json(returnlogin);
      }
      // console.log(result[0].userID); 
    });
  }
  this.findalluser = function () {
    var sql1 = 'SELECT * FROM user';
    connection.query(sql1, function (err, result) {
      if (err) {
        console.log('[SELECT ERROR] - ', err.message);
        return;
      }
      console.log(result);
    });
  }
  function addoneuser(peo, res) {
    var sql1 = 'INSERT INTO user(userName,Password) VALUES(?,?)';
    var modSqlParams1 = [peo.username, peo.password];
    connection.query(sql1, modSqlParams1, function (err, result) {
      if (err) {
        return;
      }
      var returndata = getJsonFile('./users.json5');
      returndata.data.userid = result.insertId;
      console.log(returndata);
      res.json(returndata);
    });

  }
  this.adduser = function (peo, res) {

    var sql2 = 'SELECT * FROM user where userName = ?';

    var modSqlParams2 = [peo.username];

    connection.query(sql2, modSqlParams2, function (err, result) {
      if (err) {
        return;
      }
      if (result[0]) {

      }
      else {
        addoneuser(peo, res);
      }
    });

  }
  //对比时间先后
  function check(t1, t2, t3, t4, t5, t6, y1, y2, y3, y4, y5, y6) {
    console.log(t1, y1);
    if (t1 > y1) return 1;
    if (t1 < y1) return 0;
    console.log(t2, y2);
    if (t2 > y2) return 1;
    if (t2 < y2) return 0;
    console.log(t3, y3);
    if (t3 > y3) return 1;
    if (t3 < y3) return 0;
    console.log(t4, y4);
    if (t4 > y4) return 1;
    if (t4 < y4) return 0;
    console.log(t5, y5);
    if (t5 > y5) return 1;
    if (t5 < y5) return 0;
    console.log(t6, y6);
    if (t6 > y6) return 1;
    if (t6 < y6) return 0;
    return 1;
  }
  //查询问卷
  this.findquestionnaire = function (peo, pagenum, pagesize, res) {
    console.log(pagenum, pagesize, "sql OK");
    var date = new Date();
    var year = date.getFullYear();
    var month = date.getMonth() + 1;
    var day = date.getDate();
    var hour = date.getHours();
    var minute = date.getMinutes();
    var second = date.getSeconds();
    // console.log(year,month,day,hour,minute,second);
    var sql = "SELECT * FROM questionnaire where CH = '0' and userID = ? ";
    var modSqlParams = [peo.name];


    connection.query(sql, modSqlParams, function (err, result) {
      if (err) {
        console.log(err);
        return;
      }
      var json = getJsonFile('./questionnaire.json5');
      json.data.totalpage = result.length;
      var limit = result.length;
      json.data.questionnaires = [];
      var st = (pagenum - 1) * pagesize;
      var en = pagenum * pagesize;
      if (en > limit) {
        en = result.length;
      }
      console.log("st: ", st, "pagesize: ", pagesize, "en: ", en);
      for (var i = st; i < en; ++i) {
        console.log(result[i].title);
        var t = result[i].addtime;
        var mydate = new Date(t);
        var myear = mydate.getFullYear();
        var mmonth = mydate.getMonth() + 1;
        var mday = mydate.getDate();
        var mhour = mydate.getHours();
        var mminute = mydate.getMinutes();
        var msecond = mydate.getSeconds();
        var tmp = myear + '-' + mmonth + '-' + mday + ' ' + mhour + ':' + mminute + ':' + msecond;
        result[i].addtime = tmp;

        t = result[i].releasetime;
        mydate = new Date(t);
        myear = mydate.getFullYear();
        mmonth = mydate.getMonth() + 1;
        mday = mydate.getDate();
        mhour = mydate.getHours();
        mminute = mydate.getMinutes();
        msecond = mydate.getSeconds();
        if (check(year, month, day, hour, minute, second, myear, mmonth, mday, mhour, mminute, msecond) == 1) {
          result[i].status = "发布中";
        }
        else result[i].status = "未发布";
        tmp = myear + '-' + mmonth + '-' + mday + ' ' + mhour + ':' + mminute + ':' + msecond;
        result[i].releasetime = tmp;

        t = result[i].endtime;
        mydate = new Date(t);
        myear = mydate.getFullYear();
        mmonth = mydate.getMonth() + 1;
        mday = mydate.getDate();
        mhour = mydate.getHours();
        mminute = mydate.getMinutes();
        msecond = mydate.getSeconds();
        if (check(year, month, day, hour, minute, second, myear, mmonth, mday, mhour, mminute, msecond) == 1) {
          result[i].status = "已结束";
        }
        tmp = myear + '-' + mmonth + '-' + mday + ' ' + mhour + ':' + mminute + ':' + msecond;
        result[i].endtime = tmp;
        var gg = result[i].questionnaireid;
        result[i].questionnaireid = gg.toString();
        json.data.questionnaires.push(result[i]);
      }
      res.json(json);

    });
  }
  //删除问卷
  this.delquestionnaire = function (idstodel, res) {
    var json = getJsonFile('./deleteQuestionnaire.json5');
    console.log('in del func');
    if (idstodel == null) {
      json.meta.msg = 'id为空';
      json.meta.status = 400;
      res.json(json)
      return;
    }
    var nbrOfId = idstodel.length;

    //先逐个查询
    var querySql = 'SELECT * FROM questionnaire where questionnaireid = ? ';
    for (var i = 0; i < nbrOfId; ++i) {
      var modSqlParams = [idstodel[i]];
      connection.query(querySql, modSqlParams, function (err, result) {
        if (err) {
          console.log(err);
          json.meta.msg = '未查询到所有问卷信息';
          res.json(json)
          return;
        }
      })
    }
    //所有问卷都已找到，再开始删除
    delSql = "UPDATE questionnaire SET CH='1' where questionnaireid = ? ";
    for (var i = 0; i < nbrOfId; ++i) {
      var modSqlParams = [idstodel[i]];
      connection.query(delSql, modSqlParams, function (err, result) {
        if (err) {
          console.log(err);
          json.meta.msg = '删除失败';
          res.json(json)
          return;
        }
      })
    }
    res.json(json);


  }
}
module.exports = Mysql
